package cn.com.yusys.yusp.admin.Utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 *
 * @author zhangzw5
 * @date 2019年1月3日
 * @ersion 1.0
 */
public class ExcelUtils {

    // createExcelFile

    /**
     *
     *
     * @param filePath
     * @throws IOException
     */
    public static void createExcelFile(String filePath) throws IOException {
        if (filePath == null) { // 检查文件路径
            return;
        }
        if (!filePath.endsWith(".xlsx") && !filePath.endsWith(".xls")) {
            return;
        }
        File file = new File(filePath);
        if (file.exists() || file.isFile()) { // 检查文件
            return;
        }
        String dirPath = file.getParent(); // 检查文件夹
        File dir = new File(dirPath);
        if (!dir.exists() || !dir.isDirectory()) {
            dir.mkdirs();
        }
        XSSFWorkbook workbook = new XSSFWorkbook(); // 创建文件
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(filePath);
            workbook.write(fos);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fos != null) {
                try {
                    fos.flush();
                } catch (IOException e) {

                }
                try {
                    fos.close();
                } catch (IOException e) {

                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //

    /**
     * 获取excel的sheet名称及index，忽略隐藏的sheet
     *
     * @param filePath
     * @return
     */
    public static Map<String, Integer> getSheetsInfo(String filePath) {
        Map<String, Integer> retValue = new HashMap<String, Integer>();
        File file = null;
        if (filePath == null) { // 检查文件路径
            return retValue;
        }
        if (!filePath.endsWith(".xlsx") && !filePath.endsWith(".xls")) {
            return retValue;
        }
        file = new File(filePath);
        if (!file.exists() || !file.isFile()) { // 检查文件
            return retValue;
        }
        //
        FileInputStream fis = null;
        Workbook workbook = null;
        try {
            fis = new FileInputStream(file);
            if(filePath.endsWith(".xlsx")){
                workbook = new XSSFWorkbook(fis);
                int numberOfSheets = workbook.getNumberOfSheets();
                for (int i = 0; i < numberOfSheets; i++) {
                    if (workbook.isSheetHidden(i)) {
                        continue;
                    }
                    String sheetName = workbook.getSheetName(i);
                    retValue.put(sheetName, new Integer(i));
                }
            }else {
                workbook = new HSSFWorkbook (fis);
                int numberOfSheets = workbook.getNumberOfSheets();
                for (int i = 0; i < numberOfSheets; i++) {
                    if (workbook.isSheetHidden(i)) {
                        continue;
                    }
                    String sheetName = workbook.getSheetName(i);
                    retValue.put(sheetName, new Integer(i));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (fis != null) {
                try {
                    fis.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return retValue;
    }

    // 读取excel的sheet页

    /**
     *
     *
     * @param filePath
     * @param sheetIdx
     * @return
     */
    public static String[][] read(String filePath, int sheetIdx) {
        String[][] result = null;
        File file = null;
        if (filePath == null) { // 检查文件路径
            return result;
        }
        if (!filePath.endsWith(".xlsx") && !filePath.endsWith(".xls")) {
            return result;
        }
        file = new File(filePath);
        if (!file.exists() || !file.isFile()) { // 检查文件
            return result;
        }
        //
        FileInputStream fis = null;
        Workbook workbook = null;
        try {
            fis = new FileInputStream(file);
            if(filePath.endsWith(".xlsx")){
                workbook = new XSSFWorkbook(fis);
                XSSFSheet sheet = (XSSFSheet)workbook.getSheetAt(sheetIdx);
                result = readXlsx(sheet);
            }else {
                workbook = new HSSFWorkbook (fis);
                HSSFSheet sheet = (HSSFSheet)workbook.getSheetAt(sheetIdx);
                result = readXls(sheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (fis != null) {
                try {
                    fis.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    /**
     *
     *
     * @param file
     * @param sheetIdx
     * @return
     */
    public static String[][] read(File file, int sheetIdx) {
        String[][] result = null;
        if (!file.exists() || !file.isFile()) { // 检查文件
            return result;
        }
        //
        String fileName = file.getName();
        FileInputStream fis = null;
        Workbook workbook = null;
        try {
            fis = new FileInputStream(file);
            if(fileName.endsWith(".xlsx")){
                workbook = new XSSFWorkbook(fis);
                XSSFSheet sheet = (XSSFSheet)workbook.getSheetAt(sheetIdx);
                result = readXlsx(sheet);
            }else {
                workbook = new HSSFWorkbook (fis);
                HSSFSheet sheet = (HSSFSheet)workbook.getSheetAt(sheetIdx);
                result = readXls(sheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (fis != null) {
                try {
                    fis.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    /**
     *
     *
     * @param file
     * @param sheetIdx
     * @return
     */
    public static String[][] read(MultipartFile file, int sheetIdx) {
        String[][] result = null;
        if (file == null) { // 检查文件
            return result;
        }
        //
        String fileName = file.getOriginalFilename();
        InputStream  is = null;
        Workbook workbook = null;
        try {
            is = file.getInputStream();
            if(fileName.endsWith(".xlsx")){
                workbook = new XSSFWorkbook(is);
                XSSFSheet sheet = (XSSFSheet)workbook.getSheetAt(sheetIdx);
                result = readXlsx(sheet);
            }else {
                workbook = new HSSFWorkbook(is);
                HSSFSheet sheet = (HSSFSheet)workbook.getSheetAt(sheetIdx);
                result = readXls(sheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (is != null) {
                try {
                    is.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    // 读取excel的sheet页

    /**
     *
     *
     * @param filePath
     * @param sheetName
     * @return
     */
    public static String[][] read(String filePath, String sheetName) {
        String[][] result = null;
        File file = null;
        if (filePath == null) { // 检查文件路径
            return result;
        }
        if (!filePath.endsWith(".xlsx") && !filePath.endsWith(".xls")) {
            return result;
        }
        file = new File(filePath);
        if (!file.exists() || !file.isFile()) { // 检查文件
            return result;
        }
        //
        FileInputStream fis = null;
        Workbook workbook = null;
        try {
            fis = new FileInputStream(file);
            if(filePath.endsWith(".xlsx")){
                workbook = new XSSFWorkbook(fis);
                XSSFSheet sheet = (XSSFSheet)workbook.getSheet(sheetName);
                result = readXlsx(sheet);
            }else {
                workbook = new HSSFWorkbook (fis);
                HSSFSheet sheet = (HSSFSheet)workbook.getSheet(sheetName);
                result = readXls(sheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (fis != null) {
                try {
                    fis.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    /**
     *
     *
     * @param file
     * @param sheetName
     * @return
     */
    public static String[][] read(File file, String sheetName) {
        String[][] result = null;
        if (!file.exists() || !file.isFile()) { // 检查文件
            return result;
        }
        //
        String fileName = file.getName();
        FileInputStream fis = null;
        Workbook workbook = null;
        try {
            fis = new FileInputStream(file);
            if(fileName.endsWith(".xlsx")){
                workbook = new XSSFWorkbook(fis);
                XSSFSheet sheet = (XSSFSheet)workbook.getSheet(sheetName);
                result = readXlsx(sheet);
            }else {
                workbook = new HSSFWorkbook (fis);
                HSSFSheet sheet = (HSSFSheet)workbook.getSheet(sheetName);
                result = readXls(sheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (fis != null) {
                try {
                    fis.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    /**
     *
     *
     * @param file
     * @param sheetName
     * @return
     */
    public static String[][] read(MultipartFile file, String sheetName) {
        String[][] result = null;
        if (file == null) { // 检查文件
            return result;
        }
        //
        String fileName = file.getOriginalFilename();
        InputStream  is = null;
        Workbook workbook = null;
        try {
            is = file.getInputStream();
            if(fileName.endsWith(".xlsx")){
                workbook = new XSSFWorkbook(is);
                XSSFSheet sheet = (XSSFSheet)workbook.getSheet(sheetName);
                result = readXlsx(sheet);
            }else {
                workbook = new HSSFWorkbook (is);
                HSSFSheet sheet = (HSSFSheet)workbook.getSheet(sheetName);
                result = readXls(sheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (is != null) {
                try {
                    is.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    // readXls

    /**
     *
     *
     * @param sheet
     * @return
     */
    private static String[][] readXls(HSSFSheet sheet) {
        if (sheet == null) {
            return null;
        }
        return readXlsRows(sheet);
    }

    /**
     *
     *
     * @param sheet
     * @return
     */
    private static String[][] readXlsRows(HSSFSheet sheet) {
        String[][] result = null;
        int rowNum = sheet.getPhysicalNumberOfRows();
        result = new String[rowNum][];
        for (int i = 0; i < rowNum; i++) {
            HSSFRow  row = sheet.getRow(i);
            result[i] = readXlsCells(row);
        }
        return result;
    }

    /**
     *
     *
     * @param row
     * @return
     */
    private static String[] readXlsCells(HSSFRow  row) {
        String[] result = null;
        int cellNum = row.getLastCellNum();
        result = new String[cellNum];
        for (int i = 0; i < cellNum; i++) {
            HSSFCell cell = row.getCell(i);
            result[i] = getCellValue(cell);
        }
        return result;
    }

    /**
     *
     *
     * @param cell
     * @return
     */
    private static String getCellValue(HSSFCell cell) {
        String result = null;
        if (cell == null) {
            return result;
        }
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)) {
            if (cell.getDateCellValue() != null) {
                result = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd");
            }
        } else if (cellType == CellType.NUMERIC || cellType == CellType.FORMULA) {
            result = new BigDecimal(cell.getNumericCellValue()).stripTrailingZeros().toPlainString();
        } else if (cellType == CellType.STRING) {
            result = cell.getStringCellValue();
        } else if (cellType == CellType.BOOLEAN) {
            result = String.valueOf(cell.getBooleanCellValue());
        } else if (cellType == CellType.ERROR) {
            result = "";
        } else {
            result = cell.getStringCellValue();
        }
        if (result != null) {
            result = result.trim();
        }
        return result;
    }

    // readXlsx

    /**
     *
     *
     * @param sheet
     * @return
     */
    private static String[][] readXlsx(XSSFSheet sheet) {
        if (sheet == null) {
            return null;
        }
        return readXlsxRows(sheet);
    }

    /**
     *
     *
     * @param sheet
     * @return
     */
    private static String[][] readXlsxRows(XSSFSheet sheet) {
        String[][] result = null;
        int rowNum = sheet.getPhysicalNumberOfRows();
        result = new String[rowNum][];
        for (int i = 0; i < rowNum; i++) {
            XSSFRow  row = sheet.getRow(i);
            result[i] = readXlsxCells(row);
        }
        return result;
    }

    /**
     *
     *
     * @param row
     * @return
     */
    private static String[] readXlsxCells(XSSFRow  row) {
        String[] result = null;
        if (null == row) {
            return null;
        }
        int cellNum = row.getLastCellNum();
        result = new String[cellNum];
        for (int i = 0; i < cellNum; i++) {
            XSSFCell cell = row.getCell(i);
            result[i] = getCellValue(cell);
        }
        return result;
    }

    /**
     *
     *
     * @param cell
     * @return
     */
    private static String getCellValue(XSSFCell cell) {
        String result = null;
        if (cell == null) {
            return result;
        }
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)) {
            if (cell.getDateCellValue() != null) {
                result = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd");
            }
        } else if (cellType == CellType.NUMERIC || cellType == CellType.FORMULA) {
            result = new BigDecimal(cell.getNumericCellValue()).stripTrailingZeros().toPlainString();
        } else if (cellType == CellType.STRING) {
            result = cell.getStringCellValue();
        } else if (cellType == CellType.BOOLEAN) {
            result = String.valueOf(cell.getBooleanCellValue());
        } else if (cellType == CellType.ERROR) {
            result = "";
        } else {
            result = cell.getStringCellValue();
        }
        if (result != null) {
            result = result.trim();
        }
        return result;
    }

    //

    public static void write(String filePath, String sheetName, String[] title,
                             String[][] data) {
        FileInputStream fis = null;
        FileOutputStream fos = null;
        XSSFWorkbook workbook = null;
        try {
            fis = new FileInputStream(filePath);
            workbook = new XSSFWorkbook(fis);
            XSSFSheet sheet = workbook.createSheet(sheetName);
            // 标题
            addRow(sheet, 0, title);
            // 数据
            if (data != null) {
                for (int i = 0; i < data.length; i++) {
                    addRow(sheet, i + 1, data[i]);
                }
            }
            //
            fos = new FileOutputStream(filePath);
            workbook.write(fos);
            fos.close();
            fis.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fos != null) {
                try {
                    fos.flush();
                } catch (IOException e) {

                }
                try {
                    fos.close();
                } catch (IOException e) {

                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {

                }
            }
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {

                }
            }
        }
    }

    private static void addRow(XSSFSheet sheet, int rowIdx, String[] rowData) {
        if (rowData == null) {
            return;
        }
        Row titleRow = sheet.createRow(rowIdx);
        for (int i = 0; i < rowData.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rowData[i]);
        }
    }

    //

    /**
     * 生成要导出的sheet页数据信息（sheet名称、表头、数据）
     *
     * @param sheetName
     * @param queryResult
     * @param expColInfo
     * @param lookupInfo
     * @return
     */
    public static List<Map<String, Object>> genSheetDataList(String sheetName, List<Map<String, Object>> queryResult,
                                                             ExcelExportColumnInfo[] expColInfo, Map<String, List<Map<String, String>>> lookupInfo) {
        List<Map<String, Object>> sheetDataList = new ArrayList<Map<String, Object>>();
        // 表头
        List<List<CellInfo>> header = new ArrayList<List<CellInfo>>();
        List<CellInfo> headOfRow = new ArrayList<CellInfo>();
        for (int i = 0; i < expColInfo.length; i++) {
            headOfRow.add(new CellInfo(expColInfo[i].getTitle()));
        }
        if (headOfRow.size() > 0) {
            header.add(headOfRow);
        }
        // 生产excel行数据
        List<List<CellInfo>> datalist = new ArrayList<List<CellInfo>>(); // 行数据列表
        int queryResultSize = queryResult == null ? 0 : queryResult.size();
        for (int i = 0; i < queryResultSize; i++) {
            List<CellInfo> dataOfRow = new ArrayList<CellInfo>();
            for (int j = 0; j < expColInfo.length; j++) {
                Object fieldObjValue = queryResult.get(i).get(expColInfo[j].getField());
                String fieldValue = genCellValue(fieldObjValue, expColInfo[j], lookupInfo);
                dataOfRow.add(new CellInfo(fieldValue));
            }
            datalist.add(dataOfRow);
        }

        Map<String, Object> sheetData = new HashMap<String, Object>();
        sheetData.put("sheetName", sheetName);// sheet名称
        sheetData.put("headerLength", expColInfo.length); // 表头长度
        sheetData.put("header", header);// 表头
        sheetData.put("data", datalist);// 数据
        sheetDataList.add(sheetData);
        return sheetDataList;
    }

    private static String genCellValue(Object fieldObjValue, ExcelExportColumnInfo expColInfo, Map<String, List<Map<String, String>>> lookupInfo) {
        if (fieldObjValue == null) {
            return "";
        }
        //
        String fieldValue = String.valueOf(fieldObjValue);
        if (StringUtils.isBlank(fieldValue)) {
            return "";
        }
        //
        String lookupCode = expColInfo.getLookupCode();
        if (StringUtils.isBlank(lookupCode) || lookupInfo == null) {
            return fieldValue;
        }
        //
        List<Map<String, String>> lookupItemsList = lookupInfo.get(lookupCode);
        int size = lookupItemsList == null ? 0 : lookupItemsList.size();
        if (size == 0) {
            return fieldValue;
        }
        //
        for (int i = 0; i < size; i++) {
            Map<String, String> lookupItem = lookupItemsList.get(i);
            if (lookupItem == null) {
                continue;
            }
            String lookupItemCode = lookupItem.get("lookupItemCode");
            String lookupItemName = lookupItem.get("lookupItemName");
            if (fieldValue.equals(lookupItemCode)) {
                return lookupItemName;
            }
        }
        return fieldValue;
    }

    //

    /**
     * 将数据导出到excel
     *
     * @param sheetDataList
     * @return
     * @throws IOException
     */
    public static SXSSFWorkbook exportExcel(List<Map<String, Object>> sheetDataList) throws IOException {
        // 创建工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        // 字体
        Font titleFont = workbook.createFont(); // 创建字体对象
        titleFont.setFontHeightInPoints((short) 11); // 设置字体大小
        titleFont.setFontName("微软雅黑"); // 设置为黑体字
        // 字体
        Font font = workbook.createFont(); // 创建字体对象
        font.setFontHeightInPoints((short) 11); // 设置字体大小
        font.setFontName("微软雅黑"); // 设置为黑体字
        for (Map<String, Object> map : sheetDataList) {
            String sheetName = map.get("sheetName").toString();
            int headerLength = (int) map.get("headerLength");
            List<List<CellInfo>> header = (List<List<CellInfo>>) map.get("header");
            List<List<CellInfo>> data = (List<List<CellInfo>>) map.get("data");
            // 创建Sheet
            SXSSFSheet sheet = workbook.createSheet(sheetName);
            // 设置表格默认列宽度为40个字节
            sheet.setDefaultColumnWidth(40);
            // 设置第一行 （单位信息，定制）
            int rowNum = -1;

            // 表头
            for (int i = 0; i < header.size(); i++) {
                // 行
                rowNum++;
                SXSSFRow row = sheet.createRow(rowNum);
                List<CellInfo> cols = header.get(i);

                // 创建列
                int colNum = 0;
                for (int j = 0; j < cols.size(); j++) {
                    // 设置列宽
                    sheet.setColumnWidth(j, 30 * 256);

                    //
                    SXSSFCell cell = row.createCell(colNum);
                    cell.setCellValue(cols.get(j).getContent());
                    int firstRow = rowNum;
                    int lastRow = rowNum;
                    int firstCol = colNum;
                    int lastCol = colNum;
                    boolean merge = false;
                    if (cols.get(j).getRowSpan() > 1) {
                        lastRow += cols.get(j).getRowSpan() - 1;
                        merge = true;
                    }
                    // 如果跨行则先创建被合并的单元格（主要是不创建的话，合并后的样式引用有问题）
                    if (cols.get(j).getColSpan() > 1) {
                        for (int k = 0; k < cols.get(j).getColSpan() - 1; k++) {
                            colNum++;
                            SXSSFCell tmpCell = row.createCell(colNum);
                            tmpCell.setCellValue(cols.get(j).getContent());
                        }
                        lastCol = colNum;
                        merge = true;
                    }
                    colNum++;
                    if (merge) {
                        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
                    }
                }
            }
            // 表格数据
            if (data.size() > 0) {
                for (int i = 0; i < data.size(); i++) {
                    // 行
                    rowNum++;
                    SXSSFRow row = sheet.createRow(rowNum);
                    List<CellInfo> cols = data.get(i);
                    // 创建列
                    int colNum = 0;
                    for (int j = 0; j < cols.size(); j++) {
                        SXSSFCell cell = row.createCell(colNum);
                        cell.setCellValue(cols.get(j).getContent());
                        int firstRow = rowNum;
                        int lastRow = rowNum;
                        int firstCol = colNum;
                        int lastCol = colNum;
                        boolean merge = false;
                        if (cols.get(j).getRowSpan() > 1) {
                            lastRow += cols.get(j).getRowSpan() - 1;
                            merge = true;
                        }
                        // 如果跨行则先创建被合并的单元格（主要是不创建的话，合并后的样式引用有问题）
                        if (cols.get(j).getColSpan() > 1) {
                            for (int k = 0; k < cols.get(j).getColSpan() - 1; k++) {
                                colNum++;
                                SXSSFCell tmpCell = row.createCell(colNum);
                                tmpCell.setCellValue(cols.get(j).getContent());
                            }
                            lastCol = colNum;
                            merge = true;
                        }
                        colNum++;
                        if (merge) {
                            sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
                        }
                    }
                }
            }
            rowNum++;
        }
        return workbook;
    }

}
